USE [kb.net]
GO

/****** Object:  Trigger [dbo].[Email_Archive]    Script Date: 7/1/2012 8:44:23 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:		R. Brian Amesbury
-- Create date: 18 June 2012
-- Description:	Archives an old Email Address as the new one is verified
-- =============================================
CREATE TRIGGER [dbo].[Email_Archive] 
   ON  [dbo].[Account_Email] 
   AFTER DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	DECLARE @ID int
	SET @ID = (SELECT ID FROM deleted)
	DECLARE @Email varchar(50)
	SET @Email = (SELECT Email FROM deleted)
	DECLARE @DateStart datetime2
	SET @DateStart = (SELECT ModifyDate FROM deleted)
	DECLARE @DateVerified datetime2
	SET @DateVerified = (SELECT VerifiedDate FROM deleted)
	DECLARE @DateEnd datetime2
	SET @DateEnd = (SELECT VerifiedDate FROM [dbo].[Account_Email] WHERE ID = @ID)
	DECLARE @Verified bit
	SET @Verified = (SELECT Verified FROM deleted)

	INSERT INTO [dbo].[Account_Email_History]
		(
			ID,
			Email,
			DateStart,
			DateVerified,
			DateEnd,
			Verified
		)
	VALUES
		(
			@ID,
			@Email,
			@DateStart,
			@DateVerified,
			@DateEnd,
			@Verified
		)

END




GO

